package com.ugtss.busniess.utils;
import com.alibaba.fastjson2.JSONArray;
import com.ugtss.busniess.domain.DataTable;
import com.ugtss.busniess.mapper.DatabaseMapper;
import com.ugtss.common.utils.file.FileUtils;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.http.MediaType;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Component
public class DatabaseExportUtil {

    @Autowired
    private DatabaseMapper databaseMapper;

    @Value("${sql.path}")
    private String path;

    public void getTableCreate() throws IOException {

        String filename = System.currentTimeMillis() + ".json";
        List<DataTable> result = new ArrayList<>();
        List<String> tableNames = databaseMapper.getTableName();
        for (String tableName : tableNames) {
            Map<String, String> tableCreateSql = databaseMapper.getTableCreateSql(tableName);
            List<Map<String, Object>> value = databaseMapper.getTableValue(tableName);
            DataTable dataTable = new DataTable();
            dataTable.setTable(tableCreateSql.get("Table"));
            dataTable.setCreateTable(tableCreateSql.get("Create Table"));
            dataTable.setValue(value);
            result.add(dataTable);
        }
//        String sql = this.dataTables2SQL(result);
        java.nio.file.Path filePath = Paths.get(path, filename);
        Files.createDirectories(filePath.getParent());
        FileWriter writer = new FileWriter(filePath.toFile());
        writer.write(JSONArray.toJSONString(result));
        writer.close();
    }

    private String dataTables2SQL(List<DataTable> dataTables){
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SET FOREIGN_KEY_CHECKS=0;\n");

        for (DataTable dataTable : dataTables) {
            stringBuilder.append("DROP TABLE IF EXISTS ").append(dataTable.getTable()).append("; \n");
            stringBuilder.append(dataTable.getCreateTable()).append(";").append("\n");
            List<Map<String, Object>> value = dataTable.getValue();

            for (Map<String, Object> map : value) {
                String keys = map.keySet().stream()
                        .collect(Collectors.joining(", ", "(", ")"));

                String values = map.values().stream()
                        .map(o->{
                            String string = o.toString();

                            if (string.matches("([^']*)'([^']*)'")) {
                                // 存在至少两个单引号，可以做你想做的逻辑处理
                                return "\"" + string + "\"";
                            } else {
                                return "'" + string + "'";
                            }

                        })
                        .collect(Collectors.joining(", ", "(", ")"));
                String insertSql = "INSERT INTO " + dataTable.getTable() + " " + keys + " VALUES " + values + ";";
                stringBuilder.append(insertSql).append("\n");
            }

        }
        stringBuilder.append("SET FOREIGN_KEY_CHECKS=1;\n");
        return stringBuilder.toString();
    }

    public List<String> getSqlList() {
        File directory = new File(path);
        List<String> result = new ArrayList<>();
        // 获取并打印该目录下的所有文件和子目录
        File[] filesList = directory.listFiles();
        if (filesList != null) {
            for (File file : filesList) {
                result.add(file.getName());
            }
        }
        return result;
    }

    public String downLoad(String fileName, HttpServletResponse response) throws IOException {
        response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
        FileUtils.setAttachmentResponseHeader(response, fileName);
        FileUtils.writeBytes(path+fileName, response.getOutputStream());
        return "success";
    }

    public String use(String fileName) {

        StringBuilder sqlBuilder = new StringBuilder();
        try (BufferedReader br = new BufferedReader(new FileReader(path+fileName))) {
            String line;
            while ((line = br.readLine()) != null) {
                sqlBuilder.append(line).append("\n");
            }
        } catch (IOException e) {
            System.err.println("读取文件时出错：" + e.getMessage());
            return "false";
        }
        String json = sqlBuilder.toString();
        List<DataTable> dataTables = JSONArray.parseArray(json, DataTable.class);
        for (DataTable dataTable : dataTables) {
            this.dropTable(dataTable.getTable(),dataTable.getCreateTable());
            for (Map<String, Object> stringObjectMap : dataTable.getValue()) {
                databaseMapper.insert(dataTable.getTable(),stringObjectMap);
            }
        }

        return "success";
    }

    private void dropTable(String table, String createTable) {
        databaseMapper.setKey(0);
        databaseMapper.dropTable(table);
        databaseMapper.createTable(createTable);
        databaseMapper.setKey(1);
    }

    public String delete(String fileName) {
        Path filePath = Paths.get(path + fileName);
        try {
            Files.delete(filePath);
            return "success";
        } catch (IOException e) {
            return "error";
        }
    }
}